Release 10.1A: OpenEdge Data Management:
SQL Reference

CREATE INDEX

Creates an index on the specified table using the specified columns of the table. An index improves the performance of SQL operations whose predicates are based on the indexed column. However, an index slows performance of INSERT, DELETE, and UPDATE operations.

Syntax

CREATE [ UNIQUE ] INDEX index_name ON table_name ( { column_name [ ASC | DESC ] } [, ... ] ) [ AREA area_name ]   [ PRO_DESCRIPTION value | PRO_ACTIVE {’N’|’n’} ];

UNIQUE

Does not allow the table to contain any rows with duplicate column values for the set of columns specified for that index.

index_name

Must be unique for the given table.

table_name

The name of the table on which the index is being built.

column_name [ , ... ]

The columns on which searches and retrievals will be ordered. These columns are called the index key. When more than one column is specified in the CREATE INDEX statement, a concatenated index is created.

ASC | DESC

Allows the index to be ordered as either ascending (ASC) or descending (DESC) on each column of the concatenated index. The default is ASC.

area_name

The name of the storage area where the index and its entries are stored.

PRO_DESCRIPTION value

Allows you to enter a Progress 4GL description. value is an arbitrary character string.

PRO_ACTIVE {’N’|’n’}

Indicates that the index will be created as an inactive index. Inactive indexes can be created while the database is online.

Notes

The first index you create on a table should be the most fundamental key of the table. This index (the first one created on a table) cannot be dropped except by dropping the table.
An index slows performance of INSERT, DELETE, and UPDATE operations.
Use PROUTIL to activate inactive indexes.
Use CREATE INDEX without the PRO_ACTIVE {’N’|’n’} attribute to create active indexes. Active indexes can only be created against an online database if the following conditions are met:
You run CREATE INDEX immediately after creating a table.
The index is created on the newly created table.
Both the CREATE TABLE and CREATE INDEX are performed within the same transaction (that is, no commit is performed after CREATE TABLE is run.

Examples

This example illustrates how to create a unique index on a table:

CREATE UNIQUE INDEX custindex ON customer (cust_num);

This example shows how to create an inactive word index with the description field specified:

CREATE PRO_WORD INDEX CommentsWordIdx      on pub.customer      PRO_DESCRIPTION ’Word index on comments field’      PRO_ACTIVE ’n’;

Authorization

Must have DBA privilege or INDEX privilege on the table.

Related statements

ALTER TABLE, CREATE TABLE, DROP INDEX